<?php
/**
 * Created by PhpStorm.
 * User: wuchunyin
 * Date: 2018/11/8
 * Time: 13:36
 */

namespace app\index\controller;
use app\index\base_controller\BaseController;
use app\index\service\ExcelService;
use app\index\table\ArchiveBaseTable;
use think\Db;
use think\Exception;

class Statistics extends BaseController{

    /**
     * page--资产基本统计
     */
    public function basicInfo(){
        $ym = input('ym');
        if(empty($ym)){
            $ym = '201810';
        }
        $belong = input('belong');
        if(empty($belong)){
            $belong = 'tju';
        }
        $this->assign('ym',$ym);
        $this->assign('belong',$belong);

        $showList = $this->getBasicInfo($ym,$belong);

        $this->assign('showList',$showList);
        return view('basic_info');
    }

    /**
     * page--打印资产基本统计
     */
    public function printBasicInfo(){
        $ym = input('ym');
        if(empty($ym)){
            $ym = '2018-10';
        }
        $belong = input('belong');
        if(empty($belong)){
            $belong = 'tju';
        }
        $this->assign('ym',$ym);
        $this->assign('belong',$belong);

        $showList = $this->getBasicInfo($ym,$belong);

        $this->assign('showList',$showList);
        $this->view->engine->layout('layout_popup');
        return view('basic_info_print');
    }

    /**
     * page  导出资产基本统计
     */
    public function exportBasicInfo(){
        $ym = input('ym');
        if(empty($ym)){
            $ym = '2018-10';
        }
        $belong = input('belong');
        if(empty($belong)){
            $belong = 'tju';
        }
        $this->assign('ym',$ym);
        $this->assign('belong',$belong);

        $showList = $this->getBasicInfo($ym,$belong);

        $fieldList = [
            ['title'=>'归档年月','field'=>'YM']
            ,['title'=>'所属单位','field'=>'BELONG']
            ,['title'=>'资产类别','field'=>'TYPE_NAME']
            ,['title'=>'数量','field'=>'NUM']
            ,['title'=>'价值（元）','field'=>'VALUE']
        ];

        $excel = new ExcelService();
        $excel->exportExcel($showList,'资产基本统计:'.$ym,$fieldList);
    }

    /**
     * action 获取基础统计数据
     */
    public function getBasicInfo($ym,$belong){
        if($belong == 'all'){
            $list = ArchiveBaseTable::table()
                ->field("cast(TYPE_NAME as varchar2(50)) TYPE_NAME,SUM(ASSET_NUM) ASSET_NUM
            ,SUM(ASSET_PRICE) ASSET_PRICE,SUM(ASSET_SIZE) ASSET_SIZE")
                ->where(['YEARMONTH'=>$ym])
                ->group('TYPE_NAME')
                ->select();
            $name = '合计';
        }
        else{
            if($belong == 'tju'){
                $where = ['BELONG_NAME'=>'天津大学'];
                $name = '天津大学';
            }
            if($belong == 'fz'){
                $where = ['BELONG_NAME'=>'附中'];
                $name = '附中';
            }
            if($belong == 'fx'){
                $where = ['BELONG_NAME'=>'附小'];
                $name = '附小';
            }
            if($belong == 'nrs'){
                $where = ['BELONG_NAME'=>'内燃所'];
                $name = '内燃所';
            }

            $list = ArchiveBaseTable::table()
                ->field("cast(BELONG_NAME as varchar2(20)) BELONG_NAME,
                cast(TYPE_NAME as varchar2(50)) TYPE_NAME,
                SUM(ASSET_NUM) ASSET_NUM,
                SUM(ASSET_PRICE) ASSET_PRICE,
                SUM(ASSET_SIZE) ASSET_SIZE")
                ->where(['YEARMONTH'=>$ym])
                ->where($where)
                ->group('BELONG_NAME,TYPE_NAME')
                ->select();
        }

        $showList = [
            [
                'BELONG'=>$name
                ,'TYPE_NAME' => '土地（平方米）'
                ,'NUM'=>0
                ,'VALUE'=>0
                ,'YM'=>$ym
            ]
            ,[
                'BELONG'=>$name
                ,'TYPE_NAME' => '房屋、构筑物（平方米）'
                ,'NUM'=>0
                ,'VALUE'=>0
                ,'YM'=>$ym
            ]
            ,[
                'BELONG'=>$name
                ,'TYPE_NAME' => '设备（台、件）'
                ,'NUM'=>0
                ,'VALUE'=>0
                ,'YM'=>$ym
            ]
            ,[
                'BELONG'=>$name
                ,'TYPE_NAME' => '家具（件、个）'
                ,'NUM'=>0
                ,'VALUE'=>0
                ,'YM'=>$ym
            ]
            ,[
                'BELONG'=>$name
                ,'TYPE_NAME' => '图书（本、册）'
                ,'NUM'=>0
                ,'VALUE'=>0
                ,'YM'=>$ym
            ]
            ,[
                'BELONG'=>$name
                ,'TYPE_NAME' => '合计'
                ,'NUM'=>0
                ,'VALUE'=>0
                ,'YM'=>$ym
            ]
        ];
        foreach ($list as $value){

            if($value['TYPE_NAME'] == '土地'){
                $data = [
                    'BELONG'=>$name
                    ,'TYPE_NAME' => '土地（平方米）'
                    ,'NUM'=>$value['ASSET_SIZE']
                    ,'VALUE'=>$value['ASSET_PRICE']
                    ,'YM'=>$ym
                ];
                $showList[0] = $data;
            }

            if($value['TYPE_NAME'] == '建筑'){
                $data = [
                    'BELONG'=>$name
                    ,'TYPE_NAME' => '房屋、构筑物（平方米）'
                    ,'NUM'=>$value['ASSET_SIZE']
                    ,'VALUE'=>$value['ASSET_PRICE']
                    ,'YM'=>$ym
                ];
                $showList[1] = $data;
            }

            if($value['TYPE_NAME'] == '设备'){
                $data = [
                    'BELONG'=>$name
                    ,'TYPE_NAME' => '设备（台、件）'
                    ,'NUM'=>$value['ASSET_NUM']
                    ,'VALUE'=>$value['ASSET_PRICE']
                    ,'YM'=>$ym
                ];
                $showList[2] = $data;
            }

            if($value['TYPE_NAME'] == '家具'){
                $data = [
                    'BELONG'=>$name
                    ,'TYPE_NAME' => '家具（件、个）'
                    ,'NUM'=>$value['ASSET_NUM']
                    ,'VALUE'=>$value['ASSET_PRICE']
                    ,'YM'=>$ym
                ];
                $showList[3] = $data;
            }

            if($value['TYPE_NAME'] == '图书'){
                $data = [
                    'BELONG'=>$name
                    ,'TYPE_NAME' => '图书（本、册）'
                    ,'NUM'=>$value['ASSET_NUM']
                    ,'VALUE'=>$value['ASSET_PRICE']
                    ,'YM'=>$ym
                ];
                $showList[4] = $data;
            }
        }
        $value = 0;
        for($i = 0 ; $i < 5 ;$i++){
            $value += $showList[$i]['VALUE'];
        }
        $showList[5]['VALUE'] = $value;
        for($i = 0 ; $i < 6 ;$i++){
            $showList[$i]['VALUE2'] = $showList[$i]['VALUE'];
            $showList[$i]['VALUE'] = number_format($showList[$i]['VALUE'],2,'.',',');
        }
        return $showList;
    }

    /**
     * page--资产变动统计
     */
    public function changeInfo(){
        $res = $this->getChangeInfo();
        $condition = $res['condition'];
        $fin_list = $res['fin_list'];
        //dump($fin_list);
        $this->assign('title', $condition[2]);
        $this->assign('list', $fin_list);
        $this->assign('start_yearmonth', $condition[0]);
        $this->assign('end_yearmonth', $condition[1]);
        $this->assign('statisticsType', $condition[2]);

        return view('change_info');
    }

    /**
     * 打印资产变动统计
     */
    public function changeInfoPrint() {
        $res = $this->getChangeInfo();
        $condition = $res['condition'];
        $fin_list = $res['fin_list'];
        //dump($fin_list);
        $this->assign('title', $condition[2]);
        $this->assign('list', $fin_list);
        $this->assign('start_yearmonth', $condition[0]);
        $this->assign('end_yearmonth', $condition[1]);
        $this->assign('statisticsType', $condition[2]);

        $this->view->engine->layout('layout_popup');
        return view('change_info_print');
    }

    /**
     * 获取资产变动统计信息
     */
    public function getChangeInfo() {
        $condition = array(
            empty(input('start_yearmonth'))?$this->getFirstYearMonth():input('start_yearmonth'),
            empty(input('end_yearmonth'))?$this->getPreYearMonth():input('end_yearmonth'),
            empty(input('statisticsType'))?'天津大学':input('statisticsType')
        );

        //期中计算
        $mid_table = Db::table('APP_ARCHIVE.ASSET_STAT_ALTER');

        if($condition[2] != '合计'){
            $mid_field = "cast(belong_name as varchar2(20)) belong_name,cast(type_name as varchar2(50)) type_name,stat_field_name, sum(add_buy),sum(add_turn),sum(add_other),sum(add_count),sum(cut_turn),sum(cut_discard),sum(cut_other),sum(cut_count)";
            $mid_condition = "belong_name = '$condition[2]' and yearmonth >= '$condition[0]' and yearmonth <= '$condition[1]'";
            $mid_group = "belong_name,type_name,stat_field_name";
            $mid_order = "belong_name,type_name,stat_field_name";
        }else{
            $mid_field = " cast('合计' as varchar2(50)) belong_name,cast(type_name as varchar2(50)) type_name,stat_field_name, sum(add_buy),sum(add_turn),sum(add_other),sum(add_count),sum(cut_turn),sum(cut_discard),sum(cut_other),sum(cut_count)";
            $mid_condition = "yearmonth >= '$condition[0]' and yearmonth <= '$condition[1]'";
            $mid_group = "type_name,stat_field_name";
            $mid_order = "type_name,stat_field_name";
        }

        $mid_list = $mid_table->field($mid_field)->where($mid_condition)->group($mid_group)->order($mid_order)->select();
        $mid_showList = array('0' => array(0, 0, 0, 0, 0, 0, 0, 0), '1' => array(0, 0, 0, 0, 0, 0, 0, 0), '2' => array(0, 0, 0, 0, 0, 0, 0, 0), '3' => array(0, 0, 0, 0, 0, 0, 0, 0), '4' => array(0, 0, 0, 0, 0, 0, 0, 0), '5' => array(0, 0, 0, 0, 0, 0, 0, 0), '6' => array(0, 0, 0, 0, 0, 0, 0, 0), '7' => array(0, 0, 0, 0, 0, 0, 0, 0), '8' => array(0, 0, 0, 0, 0, 0, 0, 0), '9' => array(0, 0, 0, 0, 0, 0, 0, 0), '10' => array(0, 0, 0, 0, 0, 0, 0, 0), '11' => array(0, 0, 0, 0, 0, 0, 0, 0));

        $mid_add_buy_price = 0;
        $mid_add_turn_price = 0;
        $mid_add_other_price = 0;
        $mid_add_count_price = 0;
        $mid_cut_turn_price = 0;
        $mid_cut_discard_price = 0;
        $mid_cut_other_price = 0;
        $mid_cut_count_price = 0;

        $mid_add_buy_num = 0;
        $mid_add_turn_num = 0;
        $mid_add_other_num = 0;
        $mid_add_count_num = 0;
        $mid_cut_turn_num = 0;
        $mid_cut_discard_num = 0;
        $mid_cut_other_num = 0;
        $mid_cut_count_num = 0;

        $secId = 0;
        //dump($mid_list);

        foreach ($mid_list as $key => $value) {
            switch ($mid_list[$key]['TYPE_NAME']) {
                case '土地':
                    if($mid_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 0;
                    }elseif($mid_list[$key]['STAT_FIELD_NAME']=='面积'){
                        $secId = 1;
                    }elseif($mid_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 2;
                    }
                    //dump("土地=>$mid_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '房屋、构筑物':
                    if($mid_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 3;
                    }elseif($mid_list[$key]['STAT_FIELD_NAME']=='面积'){
                        $secId = 4;
                    }elseif($mid_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 5;
                    }
                    //dump("房屋、构筑物=>$mid_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '设备':
                    if($mid_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 6;
                    }elseif($mid_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 7;
                    }
                    //dump("设备=>$mid_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '家具':
                    if($mid_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 8;
                    }elseif($mid_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 9;
                    }
                    //dump("家具=>$mid_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '图书、文物、陈列物':
                    if($mid_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 10;
                    }elseif($mid_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 11;
                    }
                    //dump("图书、文物、陈列物=>$mid_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
            }
            $mid_showList[$secId] = array($mid_list[$key]['SUM(ADD_BUY)'], $mid_list[$key]['SUM(ADD_TURN)'], $mid_list[$key]['SUM(ADD_OTHER)'], $mid_list[$key]['SUM(ADD_COUNT)'], $mid_list[$key]['SUM(CUT_TURN)'], $mid_list[$key]['SUM(CUT_DISCARD)'], $mid_list[$key]['SUM(CUT_OTHER)'], $mid_list[$key]['SUM(CUT_COUNT)']);
            if($mid_list[$key]['STAT_FIELD_NAME']=='数量'){
                $mid_add_buy_num += $mid_list[$key]['SUM(ADD_BUY)'];
                $mid_add_turn_num += $mid_list[$key]['SUM(ADD_TURN)'];
                $mid_add_other_num += $mid_list[$key]['SUM(ADD_OTHER)'];
                $mid_add_count_num += $mid_list[$key]['SUM(ADD_COUNT)'];
                $mid_cut_turn_num += $mid_list[$key]['SUM(CUT_TURN)'];
                $mid_cut_discard_num += $mid_list[$key]['SUM(CUT_DISCARD)'];
                $mid_cut_other_num += $mid_list[$key]['SUM(CUT_OTHER)'];
                $mid_cut_count_num += $mid_list[$key]['SUM(CUT_COUNT)'];
            }elseif($mid_list[$key]['STAT_FIELD_NAME']=='价值'){
                $mid_add_buy_price += $mid_list[$key]['SUM(ADD_BUY)'];
                $mid_add_turn_price += $mid_list[$key]['SUM(ADD_TURN)'];
                $mid_add_other_price += $mid_list[$key]['SUM(ADD_OTHER)'];
                $mid_add_count_price += $mid_list[$key]['SUM(ADD_COUNT)'];
                $mid_cut_turn_price += $mid_list[$key]['SUM(CUT_TURN)'];
                $mid_cut_discard_price += $mid_list[$key]['SUM(CUT_DISCARD)'];
                $mid_cut_other_price += $mid_list[$key]['SUM(CUT_OTHER)'];
                $mid_cut_count_price += $mid_list[$key]['SUM(CUT_COUNT)'];
            }

        }

        //dump($mid_showList);
        //期初计算
        $start_table = Db::table('APP_ARCHIVE.ASSET_STAT_ALTER');
        if($condition[2] != '合计'){
            $start_field = "cast(belong_name as varchar2(20)) belong_name,cast(type_name as varchar2(50)) type_name,stat_field_name,sum(month_start_num)";
            $start_condition = "belong_name = '$condition[2]' and yearmonth = '$condition[0]'";
            $start_group = "belong_name,type_name,stat_field_name";
            $start_order = "belong_name,type_name,stat_field_name";
        }else{
            $start_field = "cast('合计' as varchar2(50)) belong_name,cast(type_name as varchar2(50)) type_name,stat_field_name,sum(month_start_num)";
            $start_condition = "yearmonth = '$condition[0]'";
            $start_group = "type_name,stat_field_name";
            $start_order = "type_name,stat_field_name";
        }
        $start_list = $start_table->field($start_field)->where($start_condition)->group($start_group)->order($start_order)->select();
        //dump(M()->getLastSql());
        //dump($start_list);

        $start_showList = array('0' => array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0));
        $month_start_num_num = 0;
        $month_start_num_price = 0;

        foreach ($start_list as $key => $value) {
            switch ($start_list[$key]['TYPE_NAME']) {
                case '土地':
                    if($start_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 0;
                    }elseif($start_list[$key]['STAT_FIELD_NAME']=='面积'){
                        $secId = 1;
                    }elseif($start_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 2;
                    }
                    //dump("土地=>$start_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '房屋、构筑物':
                    if($start_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 3;
                    }elseif($start_list[$key]['STAT_FIELD_NAME']=='面积'){
                        $secId = 4;
                    }elseif($start_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 5;
                    }
                    //dump("房屋、构筑物=>$start_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '设备':
                    if($start_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 6;
                    }elseif($start_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 7;
                    }
                    //dump("设备=>$start_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '家具':
                    if($start_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 8;
                    }elseif($start_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 9;
                    }
                    //dump("家具=>$start_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '图书、文物、陈列物':
                    if($start_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 10;
                    }elseif($start_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 11;
                    }
                    //dump("图书、文物、陈列物=>$start_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
            }
            $start_showList[0][$secId] = $start_list[$key]['SUM(MONTH_START_NUM)'];

            if($start_list[$key]['STAT_FIELD_NAME']=='数量'){
                $month_start_num_num += $start_list[$key]['SUM(MONTH_START_NUM)'];
            }elseif($start_list[$key]['STAT_FIELD_NAME']=='价值'){
                $month_start_num_price += $start_list[$key]['SUM(MONTH_START_NUM)'];
            }

        }

        //dump($start_showList);

        //期末计算
        $end_table = Db::table('APP_ARCHIVE.ASSET_STAT_ALTER');
        if($condition[2] != '合计'){
            $end_field = "cast(belong_name as varchar2(20)) belong_name,cast(type_name as varchar2(50)) type_name,stat_field_name,sum(month_stop_num)";
            $end_condition = "belong_name = '$condition[2]' and yearmonth = '$condition[1]'";
            $end_group = "belong_name,type_name,stat_field_name";
            $end_order = "belong_name,type_name,stat_field_name";
        }else{
            $end_field = "cast('合计' as varchar2(50)) belong_name,cast(type_name as varchar2(50)) type_name,stat_field_name,sum(month_stop_num)";
            $end_condition = "yearmonth = '$condition[1]'";
            $end_group = "type_name,stat_field_name";
            $end_order = "type_name,stat_field_name";
        }


        $end_list = $end_table->field($end_field)->where($end_condition)->group($end_group)->order($end_order)->select();
        //dump(M()->getLastSql());
        //dump($end_list);

        $end_showList = array('0' => array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0));
        $month_stop_num_num = 0;
        $month_stop_num_price = 0;

        //dump($end_showList);

        foreach ($end_list as $key => $value) {
            switch ($end_list[$key]['TYPE_NAME']) {
                case '土地':
                    if($end_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 0;
                    }elseif($end_list[$key]['STAT_FIELD_NAME']=='面积'){
                        $secId = 1;
                    }elseif($end_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 2;
                    }
                    //dump("土地=>$end_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '房屋、构筑物':
                    if($end_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 3;
                    }elseif($end_list[$key]['STAT_FIELD_NAME']=='面积'){
                        $secId = 4;
                    }elseif($end_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 5;
                    }
                    //dump("房屋、构筑物=>$end_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '设备':
                    if($end_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 6;
                    }elseif($end_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 7;
                    }
                    //dump("设备=>$end_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '家具':
                    if($end_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 8;
                    }elseif($end_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 9;
                    }
                    //dump("家具=>$end_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '图书、文物、陈列物':
                    if($end_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 10;
                    }elseif($end_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 11;
                    }
                    //dump("图书、文物、陈列物=>$end_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
            }
            $end_showList[0][$secId] = $end_list[$key]['SUM(MONTH_STOP_NUM)'];

            if($end_list[$key]['STAT_FIELD_NAME']=='数量'){
                $month_stop_num_num += $end_list[$key]['SUM(MONTH_STOP_NUM)'];
            }elseif($end_list[$key]['STAT_FIELD_NAME']=='价值'){
                $month_stop_num_price += $end_list[$key]['SUM(MONTH_STOP_NUM)'];
            }

        }

        //dump($start_showList);
        //dump($mid_showList);
        //dump($end_showList);

        //将期初、期中、期末的数组组合，用于页面循环输出
        for($i=0;$i<=11;$i++){
            $fin_list[$i] = array(
                $start_showList[0][$i],
                $mid_showList[$i][0],
                $mid_showList[$i][1],
                $mid_showList[$i][2],
                $mid_showList[$i][3],
                $mid_showList[$i][4],
                $mid_showList[$i][5],
                $mid_showList[$i][6],
                $mid_showList[$i][7],
                $end_showList[0][$i]
            );
        }
        //合计数量
        $fin_list[12] = array(
            $month_start_num_num,
            $mid_add_buy_num,
            $mid_add_turn_num,
            $mid_add_other_num,
            $mid_add_count_num,
            $mid_cut_turn_num,
            $mid_cut_discard_num,
            $mid_cut_other_num,
            $mid_cut_count_num,
            $month_stop_num_num
        );

        //合计价值
        $fin_list[13] = array(
            $month_start_num_price,
            $mid_add_buy_price,
            $mid_add_turn_price,
            $mid_add_other_price,
            $mid_add_count_price,
            $mid_cut_turn_price,
            $mid_cut_discard_price,
            $mid_cut_other_price,
            $mid_cut_count_price,
            $month_stop_num_price
        );

        return [
            'condition'=>$condition,
            'fin_list'=>$fin_list
        ];
    }


    /**
     * page--分户分类统计
     */
    public function classifyInfo(){
        $res = $this->getClassifyInfo();
        $fin_list = $res['fin_list'];
        $college_list = $res['college_list'];
        $no_college_list = $res['no_college_list'];
        $equip_sum_asset_num = $res['equip_sum_asset_num'];
        $equip_sum_asset_price = $res['equip_sum_asset_price'];
        $furn_sum_asset_num = $res['furn_sum_asset_num'];
        $furn_sum_asset_price = $res['furn_sum_asset_price'];
        $archive_yearmonth = $res['archive_yearmonth'];
        $college_selected_list = $res['college_selected_list'];
//        dump($fin_list);
//        die();
        $row_num=count($fin_list)*2+2;
        $this->assign("college_list", $college_list);
        $this->assign("no_college_list", $no_college_list);

        $this->assign("fin_list", $fin_list);
        $this->assign('row_num',$row_num);

        $this->assign("equip_sum_asset_num", $equip_sum_asset_num);
        $this->assign("equip_sum_asset_price", $equip_sum_asset_price);
        $this->assign("furn_sum_asset_num", $furn_sum_asset_num);
        $this->assign("furn_sum_asset_price", $furn_sum_asset_price);
        $this->assign('archive_yearmonth', $archive_yearmonth);

        $this->assign("college_selected_list", $college_selected_list);
        return view('classify_info');
    }

    /**
     * page 打印分户分类统计
     */
    public function printClassifyInfo(){
        $res = $this->getClassifyInfo();
        $fin_list = $res['fin_list'];
        $equip_sum_asset_num = $res['equip_sum_asset_num'];
        $equip_sum_asset_price = $res['equip_sum_asset_price'];
        $furn_sum_asset_num = $res['furn_sum_asset_num'];
        $furn_sum_asset_price = $res['furn_sum_asset_price'];
        $archive_yearmonth = $res['archive_yearmonth'];
        $college_selected_list = $res['college_selected_list'];

        $row_num=count($fin_list)*2+2;

        $this->assign("fin_list", $fin_list);
        $this->assign('row_num',$row_num);

        $this->assign("equip_sum_asset_num", $equip_sum_asset_num);
        $this->assign("equip_sum_asset_price", $equip_sum_asset_price);
        $this->assign("furn_sum_asset_num", $furn_sum_asset_num);
        $this->assign("furn_sum_asset_price", $furn_sum_asset_price);
        $this->assign('archive_yearmonth', $archive_yearmonth);
        $this->assign("college_selected_list", $college_selected_list);

        $this->view->engine->layout('layout_popup');
        return view('classify_info_print');
    }

    /**
     * page 导出分户分类统计
     */
    public function exportClassifyInfo(){
        $ym = input('ym');
        if(empty($ym)){
            $ym = '2018-11';
        }
        $this->assign('ym',$ym);
        $showList = $this->getClassifyInfo($ym);

        $fieldList = [
            ['title'=>'归档年月','field'=>'YM']
            ,['title'=>'所属单位','field'=>'BELONG']
            ,['title'=>'下级部门','field'=>'COLLEGE']
            ,['title'=>'资产类别','field'=>'TYPE']
            ,['title'=>'数量','field'=>'NUM']
            ,['title'=>'价值（元）','field'=>'VALUE']
        ];

        $excel = new ExcelService();
        $excel->exportExcel($showList,'分类分户统计:'.$ym,$fieldList);
    }

    /**
     * action 获取分类分户统计数据
     */
    public function getClassifyInfo(){
        //获取POST信息
        $college_selected_list = input("collegeList");
        if (!empty($college_selected_list)) {
            $college_selected_list = explode(',',$college_selected_list);
            $college_selected_list = array_unique($college_selected_list);
        } else {
            $college_selected_list = [];
        }

        $condition = array(
            empty(input('archive_yearmonth'))?$this->getPreYearMonth():input('archive_yearmonth')
        );

        $college_table1 = Db::table('APP_ARCHIVE.ASSET_STAT_BASE');
        $college_table2 = Db::table('APP_ARCHIVE.ASSET_STAT_BASE');
        $college_field = "distinct cast(college_name as varchar2(50)) college_name";
        $college_condition = "belong_name = '天津大学' and (type_name = '设备' or type_name = '家具') and yearmonth like '$condition[0]%' and (college_name like '%学院' or college_name like '%学部')";
        $no_college_condition = "belong_name = '天津大学' and (type_name = '设备' or type_name = '家具') and yearmonth like '$condition[0]%' and (college_name not like '%学院' and college_name not like '%学部')";

        $college_list = $college_table1->field($college_field)->where($college_condition)->order("NLSSORT(college_name)")->select();
        $no_college_list = $college_table2->field($college_field)->where($no_college_condition)->order("NLSSORT(college_name)")->select();

        $classify_table = Db::table('APP_ARCHIVE.ASSET_STAT_BASE');
        $classify_field = "cast(belong_name as varchar2(50)) belong_name,cast(college_name as varchar2(50)) college_name,type_name,sum(asset_num),sum(asset_price)";
        $classify_group = "belong_name,college_name,type_name";
        $classify_order = "college_name asc,type_name desc";
        if(!empty($college_selected_list)){
            $classify_condition = '';
            foreach ($college_selected_list as $key => $value) {
                $classify_condition = $classify_condition." college_name = '".$value."' or";
            }
            $classify_condition = "belong_name = '天津大学' and (type_name = '设备' or type_name = '家具')  and 
      yearmonth = '$condition[0]' and (".substr($classify_condition, 0, strripos($classify_condition, 'or')).")";
        }else{
            $classify_condition = "belong_name = '天津大学' and (type_name = '设备' or type_name = '家具')  and 
      yearmonth = '$condition[0]'";
        }
        $classify_list = $classify_table->field($classify_field)->where($classify_condition)->group($classify_group)->order($classify_order)->select();

        $fin_list = [];
        $equip_sum_asset_num = 0;
        $equip_sum_asset_price = 0;
        $furn_sum_asset_num = 0;
        $furn_sum_asset_price = 0;
        for($key = 0; $key < sizeof($classify_list); ){
            $fin_list[$key] = array($classify_list[$key]['COLLEGE_NAME'],'设备',0,0,'家具',0,0);
            if($classify_list[$key]['COLLEGE_NAME']!=$classify_list[$key+1]['COLLEGE_NAME']){
                if($classify_list[$key]['TYPE_NAME']=='设备'){
                    $fin_list[$key][2] = $classify_list[$key]['SUM(ASSET_NUM)'];
                    $fin_list[$key][3] = $classify_list[$key]['SUM(ASSET_PRICE)'];

                    $equip_sum_asset_num += $fin_list[$key][2];
                    $equip_sum_asset_price += $fin_list[$key][3];
                }else{
                    $fin_list[$key][5] = $classify_list[$key]['SUM(ASSET_NUM)'];
                    $fin_list[$key][6] = $classify_list[$key]['SUM(ASSET_PRICE)'];

                    $furn_sum_asset_num += $fin_list[$key][5];
                    $furn_sum_asset_price += $fin_list[$key][6];
                }

                $key = $key + 1;
            }else{
                $fin_list[$key][2] = $classify_list[$key]['SUM(ASSET_NUM)'];
                $fin_list[$key][3] = $classify_list[$key]['SUM(ASSET_PRICE)'];
                $fin_list[$key][5] = $classify_list[$key+1]['SUM(ASSET_NUM)'];
                $fin_list[$key][6] = $classify_list[$key+1]['SUM(ASSET_PRICE)'];

                $equip_sum_asset_num += $fin_list[$key][2];
                $equip_sum_asset_price += $fin_list[$key][3];
                $furn_sum_asset_num += $fin_list[$key][5];
                $furn_sum_asset_price += $fin_list[$key][6];

                $key = $key + 2;
            }
        }

        return [
            'fin_list'=>$fin_list,
            'college_list'=>$college_list,
            'no_college_list'=>$no_college_list,
            'equip_sum_asset_num'=>$equip_sum_asset_num,
            'equip_sum_asset_price'=>$equip_sum_asset_price,
            'furn_sum_asset_num'=>$furn_sum_asset_num,
            'furn_sum_asset_price'=>$furn_sum_asset_price,
            'archive_yearmonth'=>$condition[0],
            'college_selected_list'=>$college_selected_list,
        ];

    }

    /**
     * page--大型仪器统计
     */
    public function largeInstrumentsBasicInfo(){
        // 获取统计数据
        $res = $this->getLargeInstrumentsBasicInfo();

        $fin_list = $res['fin_list'];
        $college_list = $res['college_list'];
        $no_college_list = $res['no_college_list'];
        $condition = $res['condition'];
        $total_10_sum_num = $res['total_10_sum_num'];
        $total_10_sum_price = $res['total_10_sum_price'];
        $total_40_sum_num = $res['total_40_sum_num'];
        $total_40_sum_price = $res['total_40_sum_price'];
        $total_100_sum_num = $res['total_100_sum_num'];
        $total_100_sum_price = $res['total_100_sum_price'];
        $total_200_sum_num = $res['total_200_sum_num'];
        $total_200_sum_price = $res['total_200_sum_price'];
        $college_selected_list = $res['college_selected_list'];

        $row_num=count($fin_list)*5+5;
        //dump($fin_list);
        $this->assign('college_list', $college_list);
        $this->assign('no_college_list', $no_college_list);

        $this->assign('fin_list', $fin_list);
        $this->assign('title', $condition[1]);
        $this->assign('row_num',$row_num);

        $this->assign('total_10_sum_num', $total_10_sum_num);
        $this->assign('total_10_sum_price', $total_10_sum_price);
        $this->assign('total_40_sum_num', $total_40_sum_num);
        $this->assign('total_40_sum_price', $total_40_sum_price);
        $this->assign('total_100_sum_num', $total_100_sum_num);
        $this->assign('total_100_sum_price', $total_100_sum_price);
        $this->assign('total_200_sum_num', $total_200_sum_num);
        $this->assign('total_200_sum_price', $total_200_sum_price);
        $this->assign('archive_yearmonth', empty(input('archive_yearmonth'))?$this->getPreYearMonth():input('archive_yearmonth'));
        $this->assign('statisticsType', (!input('statisticsType'))?'天津大学':input('statisticsType'));
        $this->assign('college_selected_list', $college_selected_list);

        return view('large_instruments_basic_info');
    }

    public function getLargeInstrumentsBasicInfo() {
        //获取POST信息
        $college_selected_list = input("collegeList");
        if ($college_selected_list) {
            $college_selected_list = explode(',',input("collegeList"));
        } else {
            $college_selected_list = [];
        }

        if (!empty($college_selected_list)) {
            $college_selected_list = array_unique($college_selected_list);
        }

        $condition = array(
            empty(input('archive_yearmonth'))?$this->getPreYearMonth():input('archive_yearmonth'),
            empty(input('statisticsType'))?'天津大学':input('statisticsType')
        );
        $college_table = Db::table("APP_ARCHIVE.ASSET_STAT_BASE");
        $college_table2 = Db::table("APP_ARCHIVE.ASSET_STAT_BASE");
        $college_field = "distinct cast(COLLEGE_NAME as varchar2(50)) COLLEGE_NAME";
        $college_condition = "yearmonth like '$condition[0]%' and belong_name = '$condition[1]' and 
        type_name = '设备'  and price_interval <> '10万以下' and (college_name like '%学院' or college_name like '%学部')";
        $no_college_condition = "yearmonth like '$condition[0]%' and belong_name = '$condition[1]' and 
        type_name = '设备'  and price_interval <> '10万以下' and (college_name not like '%学院' and college_name not like '%学部')";


        $college_list_sql = $college_table->field($college_field)->where($college_condition)->order("NLSSORT(college_name)")->fetchSql()->select();
        $college_list = Db::query($college_list_sql);

        $no_college_list_sql = $college_table2->field($college_field)->where($no_college_condition)->order("NLSSORT(college_name)")->fetchSql()->select();
        $no_college_list = Db::query($no_college_list_sql);

        $instru_condition = '';
        if(!empty($college_selected_list)){
            foreach ($college_selected_list as $key => $value) {
                $instru_condition = $instru_condition." college_name like '".$value."%' or";
            }
            $instru_condition = "type_name = '设备' and price_interval <> '10万以下' and
      belong_name = '$condition[1]' and yearmonth = '$condition[0]' and (".substr($instru_condition, 0, strripos($instru_condition, 'or')).")";
        }else{
            $instru_condition = "type_name = '设备' and price_interval <> '10万以下' and
      belong_name = '$condition[1]' and yearmonth = '$condition[0]'";
        }

        $instru_table = Db::table("APP_ASSET.ARCHIVE_ASSET_BASE");
        $instru_field = "cast(belong_name as varchar2(50)) belong_name,cast(college_name as varchar2(50)) college_name,price_interval,sum(asset_num),sum(asset_price)";
        $instru_group = "belong_name,college_name,price_interval";
        $instru_order = "college_name asc,price_interval desc";
        $instru_list = $instru_table->field($instru_field)->where($instru_condition)->group($instru_group)->order($instru_order)->select();

        $total_10_sum_num = 0;
        $total_10_sum_price = 0;
        $total_40_sum_num = 0;
        $total_40_sum_price = 0;
        $total_100_sum_num = 0;
        $total_100_sum_price = 0;
        $total_200_sum_num = 0;
        $total_200_sum_price = 0;
        $fin_list = [];
        for($key = 0; $key < count($instru_list);){
            $fin_list[$key] = array($instru_list[$key]['COLLEGE_NAME'],'10万-40万（含10万）',0,0,'40万-100万（含40万）',0,0,'100万-200万（含100万）',0,0,'200万以上（含200万）',0,0,0,0);
            $tmp_sum_num = 0;
            $tmp_sum_price =0;
            $collegeName1 = $instru_list[$key]['COLLEGE_NAME'];
            $collegeName2 = isset($instru_list[$key+1])?$instru_list[$key+1]['COLLEGE_NAME']:'';
            $collegeName3 = isset($instru_list[$key+2])?$instru_list[$key+2]['COLLEGE_NAME']:'';
            $collegeName4 = isset($instru_list[$key+3])?$instru_list[$key+3]['COLLEGE_NAME']:'';
            $collegeName5 = isset($instru_list[$key+4])?$instru_list[$key+4]['COLLEGE_NAME']:'';

//            if($instru_list[$key]['COLLEGE_NAME']!=$instru_list[$key+1]['COLLEGE_NAME']){
            if($collegeName1 != $collegeName2){

                //相连前后两条数据college_name不相同
                $i = 0;
                switch ($instru_list[$key]['PRICE_INTERVAL']) {
                    case '10万-40万（含10万）':
                        $fin_list[$key][2] = $instru_list[$key]['SUM(ASSET_NUM)'];
                        $fin_list[$key][3] = $instru_list[$key]['SUM(ASSET_PRICE)'];

                        $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                        $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                        $total_10_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                        $total_10_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                        break;
                    case '40万-100万（含40万）':
                        $fin_list[$key][5] = $instru_list[$key]['SUM(ASSET_NUM)'];
                        $fin_list[$key][6] = $instru_list[$key]['SUM(ASSET_PRICE)'];

                        $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                        $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                        $total_40_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                        $total_40_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                        break;
                    case '100万-200万（含100万）':
                        $fin_list[$key][8] = $instru_list[$key]['SUM(ASSET_NUM)'];
                        $fin_list[$key][9] = $instru_list[$key]['SUM(ASSET_PRICE)'];

                        $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                        $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                        $total_100_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                        $total_100_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                        break;
                    case '200万以上（含200万）':
                        $fin_list[$key][11] = $instru_list[$key]['SUM(ASSET_NUM)'];
                        $fin_list[$key][12] = $instru_list[$key]['SUM(ASSET_PRICE)'];

                        $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                        $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                        $total_200_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                        $total_200_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                        break;
                }

                $fin_list[$key][13] = $tmp_sum_num;
                $fin_list[$key][14] = $tmp_sum_price;
                $key = $key + 1;
            }
//            elseif($instru_list[$key]['COLLEGE_NAME']!=$instru_list[$key+2]['COLLEGE_NAME']){
            elseif ($collegeName1 != $collegeName3){

                //相连前后两条数据college_name相同，但是第三条不同
                for($i = 0;$i < 2;){
                    switch ($instru_list[$key]['PRICE_INTERVAL']) {
                        case '10万-40万（含10万）':
                            $fin_list[$key][2] = $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $fin_list[$key][3] = $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $total_10_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $total_10_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                            break;
                        case '40万-100万（含40万）':
                            $fin_list[$key][5] = $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $fin_list[$key][6] = $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $total_40_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $total_40_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                            break;
                        case '100万-200万（含100万）':
                            $fin_list[$key][8] = $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $fin_list[$key][9] = $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $total_100_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $total_100_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                            break;
                        case '200万以上（含200万）':
                            $fin_list[$key][11] = $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $fin_list[$key][12] = $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $total_200_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $total_200_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                            break;
                    }
                    $i = $i + 1;

                }

                $fin_list[$key][13] = $tmp_sum_num;
                $fin_list[$key][14] = $tmp_sum_price;
                $key = $key + 2;
            }
//            elseif($instru_list[$key]['COLLEGE_NAME']!=$instru_list[$key+3]['COLLEGE_NAME']){
            elseif ($collegeName1 != $collegeName4){

                // 相连前后三条数据college_name相同，但是第四条不同
                for($i = 0;$i < 3;){
                    switch ($instru_list[$key]['PRICE_INTERVAL']) {
                        case '10万-40万（含10万）':
                            $fin_list[$key][2] = $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $fin_list[$key][3] = $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $total_10_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $total_10_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                            break;
                        case '40万-100万（含40万）':
                            $fin_list[$key][5] = $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $fin_list[$key][6] = $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $total_40_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $total_40_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                            break;
                        case '100万-200万（含100万）':
                            $fin_list[$key][8] = $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $fin_list[$key][9] = $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $total_100_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $total_100_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                            break;
                        case '200万以上（含200万）':
                            $fin_list[$key][11] = $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $fin_list[$key][12] = $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $total_200_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $total_200_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                            break;
                    }
                    $i = $i + 1;

                }

                $fin_list[$key][13] = $tmp_sum_num;
                $fin_list[$key][14] = $tmp_sum_price;
                $key = $key + 3;
            }
//            elseif($instru_list[$key]['COLLEGE_NAME']!=$instru_list[$key+4]['COLLEGE_NAME']){
            elseif ($collegeName1 != $collegeName5){

                //相连前后四条数据college_name相同（全相等）
                for($i = 0;$i < 4;){
                    switch ($instru_list[$key+$i]['PRICE_INTERVAL']) {
                        case '10万-40万（含10万）':
                            $fin_list[$key][2] = $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $fin_list[$key][3] = $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $total_10_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $total_10_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                            break;
                        case '40万-100万（含40万）':
                            $fin_list[$key][5] = $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $fin_list[$key][6] = $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $total_40_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $total_40_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                            break;
                        case '100万-200万（含100万）':
                            $fin_list[$key][8] = $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $fin_list[$key][9] = $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $total_100_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $total_100_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                            break;
                        case '200万以上（含200万）':
                            $fin_list[$key][11] = $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $fin_list[$key][12] = $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $tmp_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $tmp_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];

                            $total_200_sum_num += $instru_list[$key+$i]['SUM(ASSET_NUM)'];
                            $total_200_sum_price += $instru_list[$key+$i]['SUM(ASSET_PRICE)'];
                            break;
                    }
                    $i = $i + 1;

                }

                $fin_list[$key][13] = $tmp_sum_num;
                $fin_list[$key][14] = $tmp_sum_price;
                $key =$key + 4;
            }
        }

        return [
            'fin_list'=>$fin_list,
            'college_list'=>$college_list,
            'no_college_list'=>$no_college_list,
            'condition'=>$condition,
            'total_10_sum_num'=>$total_10_sum_num,
            'total_10_sum_price'=>$total_10_sum_price,
            'total_40_sum_num'=>$total_40_sum_num,
            'total_40_sum_price'=>$total_40_sum_price,
            'total_100_sum_num'=>$total_100_sum_num,
            'total_100_sum_price'=>$total_100_sum_price,
            'total_200_sum_num'=>$total_200_sum_num,
            'total_200_sum_price'=>$total_200_sum_price,
            'college_selected_list'=>$college_selected_list
            ];
    }
    /**
     * 打印大仪数据
     */
    public function largeInstrumentsBasicInfoPrint() {
        // 获取统计数据
        $res = $this->getLargeInstrumentsBasicInfo();

        $fin_list = $res['fin_list'];
        $condition = $res['condition'];
        $total_10_sum_num = $res['total_10_sum_num'];
        $total_10_sum_price = $res['total_10_sum_price'];
        $total_40_sum_num = $res['total_40_sum_num'];
        $total_40_sum_price = $res['total_40_sum_price'];
        $total_100_sum_num = $res['total_100_sum_num'];
        $total_100_sum_price = $res['total_100_sum_price'];
        $total_200_sum_num = $res['total_200_sum_num'];
        $total_200_sum_price = $res['total_200_sum_price'];

        $row_num=count($fin_list)*5+5;

        $this->assign('fin_list',$fin_list);
        $this->assign('row_num',$row_num);
        $this->assign('title', $condition[1]);
        $this->assign('total_10_sum_num', $total_10_sum_num);
        $this->assign('total_10_sum_price', $total_10_sum_price);
        $this->assign('total_40_sum_num', $total_40_sum_num);
        $this->assign('total_40_sum_price', $total_40_sum_price);
        $this->assign('total_100_sum_num', $total_100_sum_num);
        $this->assign('total_100_sum_price', $total_100_sum_price);
        $this->assign('total_200_sum_num', $total_200_sum_num);
        $this->assign('total_200_sum_price', $total_200_sum_price);
        $this->assign('archive_yearmonth', empty(input('archive_yearmonth'))?$this->getPreYearMonth():input('archive_yearmonth'));

        $this->view->engine->layout('layout_popup');
        return view('large_instruments_basic_print');
    }


    /**
     * page--大仪变动统计
     */
    public function largeInstrumentsChangeInfo(){
        $res = $this->getLargeInstrumentsChangeInfo();
        $condition = $res['condition'];
        $fin_list = $res['fin_list'];

        $this->assign('title', $condition[2]);
        $this->assign('list', $fin_list);
        $this->assign('start_yearmonth', $condition[0]);
        $this->assign('end_yearmonth', $condition[1]);
        $this->assign('statisticsType', $condition[2]);

        return view('large_instruments_change_info');
    }

    public function getLargeInstrumentsChangeInfo() {
        $condition = array(
            empty(input('start_yearmonth'))?$this->getFirstYearMonth():input('start_yearmonth'),
            empty(input('end_yearmonth'))?$this->getPreYearMonth():input('end_yearmonth'),
            empty(input('statisticsType'))?'天津大学':input('statisticsType')
        );

        //期中计算
        $mid_table =  Db::table("APP_ARCHIVE.ASSET_STAT_ALTER");

        if($condition[2]!=='合计'){
            $mid_field = "cast(belong_name as varchar2(50)) belong_name,price_interval,stat_field_name,sum(add_buy),sum(add_turn),sum(add_other),sum(add_count),sum(cut_turn),sum(cut_discard),sum(cut_other),sum(cut_count)";

            $mid_condition = "belong_name = '$condition[2]' and yearmonth >= '$condition[0]' and yearmonth <= '$condition[1]' and price_interval <> '10万以下' and type_name = '设备'";
            $mid_group = "belong_name,price_interval,stat_field_name";
            $mid_order = "belong_name,price_interval,stat_field_name";
        }else{
            $mid_field = "'合计' as belong_name,price_interval,stat_field_name,sum(add_buy),sum(add_turn),sum(add_other),sum(add_count),sum(cut_turn),sum(cut_discard),sum(cut_other),sum(cut_count)";
            $mid_condition = "yearmonth >= '$condition[0]' and yearmonth <= '$condition[1]' and price_interval <> '10万以下' and type_name = '设备'";
            $mid_group = "price_interval,stat_field_name";
            $mid_order = "price_interval,stat_field_name";
        }

        $mid_list = $mid_table->field($mid_field)->where($mid_condition)->group($mid_group)->order($mid_order)->select();

        $mid_showList = array('0' => array(0, 0, 0, 0, 0, 0, 0, 0), '1' => array(0, 0, 0, 0, 0, 0, 0, 0), '2' => array(0, 0, 0, 0, 0, 0, 0, 0), '3' => array(0, 0, 0, 0, 0, 0, 0, 0), '4' => array(0, 0, 0, 0, 0, 0, 0, 0), '5' => array(0, 0, 0, 0, 0, 0, 0, 0), '6' => array(0, 0, 0, 0, 0, 0, 0, 0), '7' => array(0, 0, 0, 0, 0, 0, 0, 0));

        $mid_add_buy_price = 0;
        $mid_add_turn_price = 0;
        $mid_add_other_price = 0;
        $mid_add_count_price = 0;
        $mid_cut_turn_price = 0;
        $mid_cut_discard_price = 0;
        $mid_cut_other_price = 0;
        $mid_cut_count_price = 0;

        $mid_add_buy_num = 0;
        $mid_add_turn_num = 0;
        $mid_add_other_num = 0;
        $mid_add_count_num = 0;
        $mid_cut_turn_num = 0;
        $mid_cut_discard_num = 0;
        $mid_cut_other_num = 0;
        $mid_cut_count_num = 0;

        $secId = 0;

        foreach ($mid_list as $key => $value) {
            switch ($mid_list[$key]['PRICE_INTERVAL']) {
                case '10万-40万（含10万）':
                    if($mid_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 0;
                    }elseif($mid_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 1;
                    }
                    //dump("10万-40万（含10万）=>$mid_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '40万-100万（含40万）':
                    if($mid_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 2;
                    }elseif($mid_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 3;
                    }
                    //dump("40万-100万（含40万）=>$mid_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '100万-200万（含100万）':
                    if($mid_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 4;
                    }elseif($mid_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 5;
                    }
                    //dump("00万-200万（含100万）=>$mid_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '200万以上（含200万）':
                    if($mid_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 6;
                    }elseif($mid_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 7;
                    }
                    //dump("200万以上（含200万）=>$mid_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
            }
            $mid_showList[$secId] = array($mid_list[$key]['SUM(ADD_BUY)'], $mid_list[$key]['SUM(ADD_TURN)'], $mid_list[$key]['SUM(ADD_OTHER)'], $mid_list[$key]['SUM(ADD_COUNT)'], $mid_list[$key]['SUM(CUT_TURN)'], $mid_list[$key]['SUM(CUT_DISCARD)'], $mid_list[$key]['SUM(CUT_OTHER)'], $mid_list[$key]['SUM(CUT_COUNT)']);
            if($mid_list[$key]['STAT_FIELD_NAME']=='数量'){
                $mid_add_buy_num += $mid_list[$key]['SUM(ADD_BUY)'];
                $mid_add_turn_num += $mid_list[$key]['SUM(ADD_TURN)'];
                $mid_add_other_num += $mid_list[$key]['SUM(ADD_OTHER)'];
                $mid_add_count_num += $mid_list[$key]['SUM(ADD_COUNT)'];
                $mid_cut_turn_num += $mid_list[$key]['SUM(CUT_TURN)'];
                $mid_cut_discard_num += $mid_list[$key]['SUM(CUT_DISCARD)'];
                $mid_cut_other_num += $mid_list[$key]['SUM(CUT_OTHER)'];
                $mid_cut_count_num += $mid_list[$key]['SUM(CUT_COUNT)'];
            }elseif($mid_list[$key]['STAT_FIELD_NAME']=='价值'){
                $mid_add_buy_price += $mid_list[$key]['SUM(ADD_BUY)'];
                $mid_add_turn_price += $mid_list[$key]['SUM(ADD_TURN)'];
                $mid_add_other_price += $mid_list[$key]['SUM(ADD_OTHER)'];
                $mid_add_count_price += $mid_list[$key]['SUM(ADD_COUNT)'];
                $mid_cut_turn_price += $mid_list[$key]['SUM(CUT_TURN)'];
                $mid_cut_discard_price += $mid_list[$key]['SUM(CUT_DISCARD)'];
                $mid_cut_other_price += $mid_list[$key]['SUM(CUT_OTHER)'];
                $mid_cut_count_price += $mid_list[$key]['SUM(CUT_COUNT)'];
            }

        }

        //dump($mid_showList);
        //期初计算
        $start_table = Db::table("APP_ARCHIVE.ASSET_STAT_ALTER");
        if(!empty($condition[2])){
            $start_field = "cast(belong_name as varchar2(50)) belong_name,price_interval,stat_field_name,sum(month_start_num)";
            $start_condition = "belong_name = '$condition[2]' and yearmonth = '$condition[0]' and price_interval <> '10万以下' and type_name = '设备'";
            $start_group = "belong_name,price_interval,stat_field_name";
            $start_order = "belong_name,price_interval,stat_field_name";
        }else{
            $start_field = "'合计' as belong_name,price_interval,stat_field_name,sum(month_start_num)";
            $start_condition = "yearmonth = '$condition[0]' and price_interval <> '10万以下' and type_name = '设备'";
            $start_group = "price_interval,stat_field_name";
            $start_order = "price_interval,stat_field_name";
        }
        $start_list = $start_table->field($start_field)->where($start_condition)->group($start_group)->order($start_order)->select();

        $start_showList = array('0' => array(0, 0, 0, 0, 0, 0, 0, 0));
        $month_start_num_num = 0;
        $month_start_num_price = 0;

        foreach ($start_list as $key => $value) {
            switch ($start_list[$key]['PRICE_INTERVAL']) {
                case '10万-40万（含10万）':
                    if($start_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 0;
                    }elseif($start_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 1;
                    }
                    //dump("10万-40万（含10万）=>$start_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '40万-100万（含40万）':
                    if($start_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 2;
                    }elseif($start_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 3;
                    }
                    //dump("40万-100万（含40万）=>$start_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '100万-200万（含100万）':
                    if($start_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 4;
                    }elseif($start_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 5;
                    }
                    //dump("100万-200万（含100万）=>$start_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '200万以上（含200万）':
                    if($start_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 6;
                    }elseif($start_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 7;
                    }
                    //dump("200万以上（含200万）=>$start_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
            }
            $start_showList[0][$secId] = $start_list[$key]['SUM(MONTH_START_NUM)'];

            if($start_list[$key]['STAT_FIELD_NAME']=='数量'){
                $month_start_num_num += $start_list[$key]['SUM(MONTH_START_NUM)'];
            }elseif($start_list[$key]['STAT_FIELD_NAME']=='价值'){
                $month_start_num_price += $start_list[$key]['SUM(MONTH_START_NUM)'];
            }

        }

        //dump($start_showList);

        //期末计算
        $end_table = Db::table("APP_ARCHIVE.ASSET_STAT_ALTER");
        if(!empty($condition[2])){
            $end_field = "cast(belong_name as varchar2(50)) belong_name,price_interval,stat_field_name,sum(month_stop_num)";
            $end_condition = "belong_name = '$condition[2]' and yearmonth = '$condition[1]' and price_interval <> '10万以下' and type_name = '设备'";
            $end_group = "belong_name,price_interval,stat_field_name";
            $end_order = "belong_name,price_interval,stat_field_name";
        }else{
            $end_field = "'合计' as belong_name,price_interval,stat_field_name,sum(month_stop_num)";
            $end_condition = "yearmonth = '$condition[1]' and price_interval <> '10万以下' and type_name = '设备'";
            $end_group = "price_interval,stat_field_name";
            $end_order = "price_interval,stat_field_name";
        }


        $end_list = $end_table->field($end_field)->where($end_condition)->group($end_group)->order($end_order)->select();

        $end_showList = array('0' => array(0, 0, 0, 0, 0, 0, 0, 0));
        $month_stop_num_num = 0;
        $month_stop_num_price = 0;


        foreach ($end_list as $key => $value) {
            switch ($end_list[$key]['PRICE_INTERVAL']) {
                case '10万-40万（含10万）':
                    if($end_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 0;
                    }elseif($end_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 1;
                    }
                    //dump("10万-40万（含10万）=>$end_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '40万-100万（含40万）':
                    if($end_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 2;
                    }elseif($end_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 3;
                    }
                    //dump("40万-100万（含40万）=>$end_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '100万-200万（含100万）':
                    if($end_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 4;
                    }elseif($end_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 5;
                    }
                    //dump("100万-200万（含100万）=>$end_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
                case '200万以上（含200万）':
                    if($end_list[$key]['STAT_FIELD_NAME']=='数量'){
                        $secId = 6;
                    }elseif($end_list[$key]['STAT_FIELD_NAME']=='价值'){
                        $secId = 7;
                    }
                    //dump("200万以上（含200万）=>$end_list[$key]['STAT_FIELD_NAME']".$secId);
                    break;
            }
            $end_showList[0][$secId] = $end_list[$key]['SUM(MONTH_STOP_NUM)'];

            if($end_list[$key]['STAT_FIELD_NAME']=='数量'){
                $month_stop_num_num += $end_list[$key]['SUM(MONTH_STOP_NUM)'];
            }elseif($end_list[$key]['STAT_FIELD_NAME']=='价值'){
                $month_stop_num_price += $end_list[$key]['SUM(MONTH_STOP_NUM)'];
            }

        }


        //将期初、期中、期末的数组组合，用于页面循环输出
        for($i=0;$i<=7;$i++){
            $fin_list[$i] = array(
                $start_showList[0][$i],
                $mid_showList[$i][0],
                $mid_showList[$i][1],
                $mid_showList[$i][2],
                $mid_showList[$i][3],
                $mid_showList[$i][4],
                $mid_showList[$i][5],
                $mid_showList[$i][6],
                $mid_showList[$i][7],
                $end_showList[0][$i]
            );
        }
        //合计数量
        $fin_list[8] = array(
            $month_start_num_num,
            $mid_add_buy_num,
            $mid_add_turn_num,
            $mid_add_other_num,
            $mid_add_count_num,
            $mid_cut_turn_num,
            $mid_cut_discard_num,
            $mid_cut_other_num,
            $mid_cut_count_num,
            $month_stop_num_num
        );

        //合计价值
        $fin_list[9] = array(
            $month_start_num_price,
            $mid_add_buy_price,
            $mid_add_turn_price,
            $mid_add_other_price,
            $mid_add_count_price,
            $mid_cut_turn_price,
            $mid_cut_discard_price,
            $mid_cut_other_price,
            $mid_cut_count_price,
            $month_stop_num_price
        );

        return [
            'condition'=>$condition,
            'fin_list'=>$fin_list
        ];
    }
    /**
     * 打印大仪变动表格
     */
    public function printLargeInstrumentsChange() {
        $res = $this->getLargeInstrumentsChangeInfo();
        $condition = $res['condition'];
        $fin_list = $res['fin_list'];

        $this->assign('title', $condition[2]);
        $this->assign('list', $fin_list);
        $this->assign('start_yearmonth', $condition[0]);
        $this->assign('end_yearmonth', $condition[1]);
        $this->assign('statisticsType', $condition[2]);

        $this->view->engine->layout('layout_popup');
        return view('large_instruments_change_print');
    }

    /**
     * page--年度月度统计
     */
    public function yearMonthInfo(){
        $condition = array(
            empty(input('start_yearmonth'))?date('Y'):input('start_yearmonth'),
            empty(input('end_yearmonth'))?date('Y'):input('end_yearmonth')
        );

        //dump($condition);
//        $year_table = Db::table('APP_ARCHIVE.ASSET_STAT_BASE');
        for($year = $condition[0]; $year <= $condition[1]; ++$year){
            $year_month =  Db::table('APP_ARCHIVE.ASSET_STAT_BASE')->field('max(yearmonth)')->where("yearmonth like '$year%'")->select();
            //dump(M()->getlastsql());
            $year_list[$year] = array($year, isset($year_month[0]['MAX(YEARMONTH)'])?$year_month[0]['MAX(YEARMONTH)']:0, 0, 0);
            if(isset($year_month[0]['MAX(YEARMONTH)'])){
                $tmp = $year_month[0]['MAX(YEARMONTH)'];
                $list = Db::table('APP_ARCHIVE.ASSET_STAT_BASE')->field('sum(asset_num),sum(asset_price)')->where("yearmonth = $tmp")->select();
                //dump($list);
                $year_list[$year][2] = $list[0]['SUM(ASSET_NUM)'];
                $year_list[$year][3] = round($list[0]['SUM(ASSET_PRICE)']/10000,2);
                $max_year = $year;
            }
        }
        //默认取可用的年份最大值，如果传参数，判断后赋值给$max_year
        if(!empty(input('fYear'))){
            $max_year = input('fYear');
        }

        if(isset($year_list[$max_year][1])&&$year_list[$max_year][1]!=0){
            $max_month = substr($year_list[$max_year][1], 4, 2);
            for($key = 1; $key <= $max_month; ++$key){
                $month_list[$key] = array($max_year.'年'.$key.'月',0,0);
                if($key<10){
                    $tmp = $max_year.'0'.$key;
                }else{
                    $tmp = $max_year.$key;
                }
                $month_sum_list =  Db::table('APP_ARCHIVE.ASSET_STAT_BASE')->field('sum(asset_num),sum(asset_price)')->where("yearmonth = $tmp")->select();
                $month_list[$key][1] = isset($month_sum_list[0]['SUM(ASSET_NUM)'])?$month_sum_list[0]['SUM(ASSET_NUM)']:0;
                $month_list[$key][2] = isset($month_sum_list[0]['SUM(ASSET_PRICE)'])?round($month_sum_list[0]['SUM(ASSET_PRICE)']/10000,2):0;
            }
        }


        $this->assign('year_list', $year_list);
        $this->assign('month_list', $month_list);
        $this->assign('start_yearmonth', $condition[0]);
        $this->assign('end_yearmonth', $condition[1]);

        return view('year_month_info');
    }

    protected function getFirstYearMonth() {
        return '201801';
        return date('Y')."01";
    }

    protected function getCurYearMonth() {
        return '201810';
        return date('Ym');
    }

    protected function getPreYearMonth() {
//        $curr_day = date('d'); // 返回本月的第几天
//        $prev_month = date('Ym', time() - ($curr_day * 24 * 60 * 60));
        $prev_month = date('Ym');
        $prev_month = '201810';
        return $prev_month;
    }
}